using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Emlyn.Common.Data.SqlServer.SqlManagers;
using Emlyn.Common.Data.SqlServer.DataLayer;

namespace Emlyn.Website.Articles
{
    public class ArticleDataLayer: SimpleTableDataLayer<Article, Guid>
    {
        public DataSet GetArticleByArticleCode(string aArticleCode)
        {
            string lSql = "select * from tblEOArticle where ArticleCode=@ArticleCode";
            SqlParameter lArticleCodeParameter = new SqlParameter("@ArticleCode", aArticleCode);
            List<SqlParameter> lParameters = new List<SqlParameter>();
            lParameters.Add(lArticleCodeParameter);

            return SqlManager.GetDataSet(lSql, lParameters);
        }

        public DataSet GetArticlesByParentArticleID(Guid aParentArticleID)
        {
            string lSql =
                "select a.* " +
                "from tblEOArticle a " +
                "join tblEOArticle_Article aa " +
                "on a.ArticleID = aa.ChildArticleID " +
                "where aa.ParentArticleID=@ParentArticleID " +
                "order by aa.OrderNum ";
            SqlParameter lParentArticleID = new SqlParameter("@ParentArticleID", aParentArticleID);
            List<SqlParameter> lParameters = new List<SqlParameter>();
            lParameters.Add(lParentArticleID);

            return SqlManager.GetDataSet(lSql, lParameters);
        }

        public double GetLastOrdernumForParentArticleID(Guid aParentArticleID)
        {
            double retval = 0.0;

            string lSql =
                "select max(aa.OrderNum) " +
                "from tblEOArticle_Article aa " +
                "where aa.ParentArticleID=@ParentArticleID ";
            SqlParameter lParentArticleID = new SqlParameter("@ParentArticleID", aParentArticleID);
            List<SqlParameter> lParameters = new List<SqlParameter>();
            lParameters.Add(lParentArticleID);

            DataSet lds = SqlManager.GetDataSet(lSql, lParameters);

            if (
                (lds.Tables.Count > 0) &&
                (lds.Tables[0].Rows.Count > 0) &&
                (lds.Tables[0].Columns.Count > 0) &&
                (lds.Tables[0].Rows[0][0] != DBNull.Value)
                )
            {

                retval = Convert.ToDouble(lds.Tables[0].Rows[0][0]); // just let the exception go if this is the wrong type
            }

            return retval;
        }

        public DataSet GetArticlesNotForParentArticleID(Guid aParentArticleID)
        {
            string lSql =
                "select a.* " +
                "from tblEOArticle a " +
                "left join " +
                "(select * from tblEOArticle_Article where ParentArticleID=@ParentArticleID) aa " +
                "on a.ArticleID = aa.ChildArticleID " +
                "where aa.ChildArticleID is null " +
                "order by a.ArticleName desc ";
            SqlParameter lParentArticleID = new SqlParameter("@ParentArticleID", aParentArticleID);
            List<SqlParameter> lParameters = new List<SqlParameter>();
            lParameters.Add(lParentArticleID);

            return SqlManager.GetDataSet(lSql, lParameters);
        }

        public DataSet GetArticlesWithNoParent()
        {
            string lSql =
                "select a.* " +
                "from tblEOArticle a " +
                "left join tblEOArticle_Article aa " +
                "on a.ArticleID = aa.ChildArticleID " +
                "where aa.ChildArticleID is null " +
                "order by a.ArticleName ";

            return SqlManager.GetDataSet(lSql);
        }

        public DataSet GetArticlesByChildArticleID(Guid aChildArticleID)
        {
            string lSql =
                "select a.* " +
                "from tblEOArticle a " +
                "join tblEOArticle_Article aa " +
                "on a.ArticleID = aa.ParentArticleID " +
                "where aa.ChildArticleID=@ChildArticleID " +
                "order by a.ArticleName desc ";
            SqlParameter lParentArticleID = new SqlParameter("@ChildArticleID", aChildArticleID);
            List<SqlParameter> lParameters = new List<SqlParameter>();
            lParameters.Add(lParentArticleID);

            return SqlManager.GetDataSet(lSql, lParameters);
        }

        public DataSet GetArticlesNotForChildArticleID(Guid aChildArticleID)
        {
            string lSql =
                "select a.* " +
                "from tblEOArticle a " +
                "left join " +
                "(select * from tblEOArticle_Article where ChildArticleID=@ChildArticleID) aa " +
                "on a.ArticleID = aa.ParentArticleID " +
                "where aa.ParentArticleID is null " +
                "order by a.ArticleName desc ";
            SqlParameter lParentArticleID = new SqlParameter("@ChildArticleID", aChildArticleID);
            List<SqlParameter> lParameters = new List<SqlParameter>();
            lParameters.Add(lParentArticleID);

            return SqlManager.GetDataSet(lSql, lParameters);
        }

        public void AddArticleRelationship(Guid aParentArticleID, Guid aChildArticleID)
        {
            double llastOrderNum = GetLastOrdernumForParentArticleID(aParentArticleID);

            string lSql =
                "insert tblEOArticle_Article " +
                "(Article_ArticleID, ParentArticleID, ChildArticleID, OrderNum) " +
                "values " +
                "(@Article_ArticleID, @ParentArticleID, @ChildArticleID, @OrderNum) ";

            SqlParameter lParameter;
            List<SqlParameter> lParameters = new List<SqlParameter>();

            lParameter = new SqlParameter("@Article_ArticleID", Guid.NewGuid());
            lParameters.Add(lParameter);

            lParameter = new SqlParameter("@ParentArticleID", aParentArticleID);
            lParameters.Add(lParameter);

            lParameter = new SqlParameter("@ChildArticleID", aChildArticleID);
            lParameters.Add(lParameter);

            lParameter = new SqlParameter("@OrderNum", llastOrderNum+1);
            lParameters.Add(lParameter);

            SqlManager.Execute(lSql, lParameters);
        }

        public double GetOrderNumForArticleRelationship(Guid aParentArticleID, Guid aChildArticleID)
        {
            double retval = 0.0;

            string lSql =
                "select aa.OrderNum " +
                "from tblEOArticle_Article aa " +
                "where aa.ParentArticleID=@ParentArticleID " +
                "  and aa.ChildArticleID=@ChildArticleID ";
            List<SqlParameter> lParameters = new List<SqlParameter>();

            SqlParameter lParentArticleID = new SqlParameter("@ParentArticleID", aParentArticleID);
            lParameters.Add(lParentArticleID);

            SqlParameter lChildArticleID = new SqlParameter("@ChildArticleID", aChildArticleID);
            lParameters.Add(lChildArticleID);

            DataSet lds = SqlManager.GetDataSet(lSql, lParameters);

            if (
                (lds.Tables.Count > 0) &&
                (lds.Tables[0].Rows.Count > 0) &&
                (lds.Tables[0].Columns.Count > 0) &&
                (lds.Tables[0].Rows[0][0] != DBNull.Value)
                )
            {
                retval = Convert.ToDouble(lds.Tables[0].Rows[0][0]); // just let the exception go if this is the wrong type
            }

            return retval;
        }

        public void SetOrderNumForArticleRelationship(Guid aParentArticleID, Guid aChildArticleID, double aOrderNum)
        {
            double llastOrderNum = GetLastOrdernumForParentArticleID(aParentArticleID);

            string lSql =
                "update tblEOArticle_Article " +
                "set " +
                " OrderNum=@OrderNum " +
                "where ParentArticleID=@ParentArticleID " +
                "  and ChildArticleID=@ChildArticleID ";

            SqlParameter lParameter;
            List<SqlParameter> lParameters = new List<SqlParameter>();

            lParameter = new SqlParameter("@ParentArticleID", aParentArticleID);
            lParameters.Add(lParameter);

            lParameter = new SqlParameter("@ChildArticleID", aChildArticleID);
            lParameters.Add(lParameter);

            lParameter = new SqlParameter("@OrderNum", aOrderNum);
            lParameters.Add(lParameter);

            SqlManager.Execute(lSql, lParameters);
        }

        public void RemoveArticleRelationship(Guid aParentArticleID, Guid aChildArticleID)
        {
            string lSql =
                "delete tblEOArticle_Article " +
                "where ParentArticleID = @ParentArticleID " +
                "and ChildArticleID = @ChildArticleID ";

            SqlParameter lParameter;
            List<SqlParameter> lParameters = new List<SqlParameter>();

            lParameter = new SqlParameter("@ParentArticleID", aParentArticleID);
            lParameters.Add(lParameter);

            lParameter = new SqlParameter("@ChildArticleID", aChildArticleID);
            lParameters.Add(lParameter);

            SqlManager.Execute(lSql, lParameters);
        }        
        
        protected override string TableName()
        {
            return "tblEOArticle";
        }

        protected override string KeyName()
        {
            return "ArticleID";
        }

        protected override IList<string> NonKeyAttributeNamesWritable()
        {
            List<string> retval = new List<string>();

            retval.Add("ArticleCode");
            retval.Add("ArticleName");
            retval.Add("Url");
            retval.Add("ArticleContent");

            return retval;
        }

        protected override IList<string> NonKeyAttributeNamesReadonly()
        {
            List<string> retval = new List<string>();

            retval.Add("LastChanged");

            return retval;
        }

        protected override Guid DoGetKey(Article aInstance)
        {
            return aInstance.ArticleID;
        }

        protected override object GetParameterValue(Article aInstance, string aParameterName)
        {
            switch (aParameterName)
            {
                case "ArticleCode": return aInstance.ArticleCode; 
                case "ArticleName": return aInstance.ArticleName; 
                case "Url": return aInstance.Url; 
                case "ArticleContent": return aInstance.ArticleContent;
                case "LastChanged": return aInstance.LastChanged;
            }

            throw new ApplicationException("Unknown Parameter Requested: " + aParameterName);
        }
    }
}
